# /lab_booking/crud.py

from datetime import datetime, date,timedelta,timezone
from typing import List, Optional, Union
from sqlalchemy import or_,case 
from sqlalchemy.orm import Session
import math
# 导入项目内部模块
from . import models, schemas

#     这是一个 UTC+8:00 的固定偏移时区
beijing_tz = timezone(timedelta(hours=8), name='Asia/Shanghai')



# ==========================================================
# === 设备与分类 (Equipment & Categories) - (保持不变) ===
# ==========================================================
def get_device_categories(db: Session):
    return db.query(models.DeviceCategory).all()

def get_all_devices(db: Session):
    return db.query(models.Device).all()

def get_devices_by_category(db: Session, category_id: int):
    return db.query(models.Device).filter(models.Device.category_id == category_id).all()

def get_device_by_id(db: Session, device_id: int):
    return db.query(models.Device).filter(models.Device.id == device_id).first()

# ==========================================================
# === 用户 (Users) - (保持不变) ===
# ==========================================================
def get_user_by_openid(db: Session, openid: str):
    return db.query(models.User).filter(models.User.openid == openid).first()

def create_or_update_user(db: Session, user_info: schemas.UserLogin, openid: str):
    db_user = get_user_by_openid(db, openid=openid)
    if db_user:
        db_user.name = user_info.name
        db_user.avatar_url = user_info.avatar_url
    else:
        db_user = models.User(openid=openid, name=user_info.name, avatar_url=user_info.avatar_url)
        db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user

# ====================================================================
# === 预约与订单 (Reservations & Orders) - 【全新重构逻辑】 ===
# ====================================================================

# --- 查询函数 ---

def get_reservations_by_device_and_date(db: Session, device_id: int, target_date: date):
    """
    (公共) 获取指定设备在特定日期的所有有效预约 (confirmed 和 ongoing)
    用于 device-detail 页面判断时间是否已被占用
    """
    # 1. 定义查询区间的开始时间 (当天 00:00:00)
    start_of_day = datetime.combine(target_date, datetime.min.time())
    
    # 2. 定义查询区间的结束时间 (第二天 00:00:00)
    #    使用 timedelta(days=1) 来精确计算第二天的日期
    next_day = target_date + timedelta(days=1)
    end_of_day_exclusive = datetime.combine(next_day, datetime.min.time())

    # 3. 构建查询
    #    - 查询所有 start_time 在 [当天0点, 第二天0点) 这个左闭右开区间内的预约
    #    - 并且预约状态是 'confirmed' 或 'ongoing'
    return db.query(models.Reservation).filter(
        models.Reservation.device_id == device_id,
        models.Reservation.status.in_(['confirmed', 'ongoing']),
        models.Reservation.start_time >= start_of_day,
        models.Reservation.start_time < end_of_day_exclusive
    ).all()




def get_user_reservations_for_booking_list(db: Session, user_id: int) -> List[models.Reservation]:
    """
    【核心】获取“预约订单”页面所需的所有预约记录，并实现复杂排序。
    排序规则: 1. 按状态优先级 (进行中 > 待使用 > 其他)
             2. 在同状态下，按创建时间降序 (最新的在前)
    """
    # 1. 定义状态的排序权重
    #    我们创建一个 CASE 表达式，它会为每一行数据生成一个 status_order 值
    status_order = case(
        (models.Reservation.status == 'ongoing', 1),   # "进行中" 权重为 1
        (models.Reservation.status == 'confirmed', 2),  # "待使用" 权重为 2
        else_=3                                        # 其他所有状态权重为 3
    )

    # 2. 执行查询
    return db.query(models.Reservation).filter(
        models.Reservation.creator_user_id == user_id,
        models.Reservation.status != 'completed'
    ).order_by(
        status_order.asc(),                             # 【第一排序条件】按我们定义的权重升序排列
        models.Reservation.created_at.desc()            # 【第二排序条件】在权重相同时，按创建时间降序排列
    ).all()


def get_ongoing_usages(db: Session, user_id: int) -> List[models.UsageRecord]:
    """获取“进行中”页面所需的使用记录 (结束时间为空)"""
    return db.query(models.UsageRecord).join(models.Reservation).filter(
        models.Reservation.creator_user_id == user_id,
        models.UsageRecord.actual_end_time == None
    ).order_by(models.UsageRecord.created_at.desc()).all()

def get_history_usages(db: Session, user_id: int) -> List[models.UsageRecord]:
    """获取“全部订单(历史)”页面所需的使用记录 (已完成的)"""
    return db.query(models.UsageRecord).join(models.Reservation).filter(
        models.Reservation.creator_user_id == user_id,
        models.Reservation.status == 'completed',
        models.UsageRecord.actual_end_time != None
    ).order_by(models.UsageRecord.actual_end_time.desc()).all()


# --- 操作/状态变更函数 ---

def create_reservation(db: Session, reservation: schemas.ReservationCreate, creator_user_id: int):
    """(逻辑不变) 创建一条新的预约记录，初始状态为 'confirmed'"""
    existing_reservation = db.query(models.Reservation).filter(
        models.Reservation.device_id == reservation.device_id,
        models.Reservation.status.in_(['confirmed', 'ongoing']),
        models.Reservation.start_time < reservation.end_time,
        models.Reservation.end_time > reservation.start_time
    ).first()
    if existing_reservation:
        return None
    db_reservation = models.Reservation(**reservation.model_dump(), creator_user_id=creator_user_id)
    db.add(db_reservation)
    db.commit()
    db.refresh(db_reservation)
    return db_reservation


def cancel_reservation(db: Session, reservation_id: int, user_id: int) -> Optional[models.Reservation]:
    """将预约状态从 'confirmed' 修改为 'user_cancelled'"""
    db_reservation = db.query(models.Reservation).filter(
        models.Reservation.id == reservation_id, 
        models.Reservation.creator_user_id == user_id
    ).first()
    if db_reservation and db_reservation.status == 'confirmed':
        db_reservation.status = 'user_cancelled'
        db.commit()
        db.refresh(db_reservation)
        return db_reservation
    return None


def start_usage(db: Session, reservation_id: int, user_id: int):
    """
    “去使用”操作的核心逻辑：
    1. 将预约 (Reservation) 状态更新为 'ongoing'。
    2. 创建一条新的使用记录 (UsageRecord)。
    """
    db_reservation = db.query(models.Reservation).filter(
        models.Reservation.id == reservation_id,
        models.Reservation.creator_user_id == user_id,
        models.Reservation.status == 'confirmed'
    ).first()
    if not db_reservation:
        return None # 预约不存在或状态不正确

    # 检查是否已存在使用记录，防止重复点击“开始”
    if db.query(models.UsageRecord).filter(models.UsageRecord.reservation_id == reservation_id).first():
        return "already_started"

    # 1. 更新预约状态为“进行中”
    db_reservation.status = 'ongoing'
    
    # 2. 创建新的使用记录
    db_usage_record = models.UsageRecord(
        reservation_id=reservation_id,
        actual_user_id=user_id, # 默认为预约创建者
        actual_start_time=datetime.now(beijing_tz)
    )
    db.add(db_usage_record)
    
    db.commit()
    db.refresh(db_usage_record)
    return db_usage_record


def end_usage(db: Session, usage_record_id: int, usage_data: schemas.UsageRecordEnd, user_id: int) -> Optional[models.UsageRecord]:
    """
    “结束使用”操作的核心逻辑：
    1. 填充使用记录 (UsageRecord) 的结束信息。
    2. 将关联的预约 (Reservation) 状态更新为 'completed'。
    """
    # 1. 查询到要更新的使用记录
    db_usage_record = db.query(models.UsageRecord).join(models.Reservation).filter(
        models.UsageRecord.id == usage_record_id,
        models.Reservation.creator_user_id == user_id,
         models.UsageRecord.actual_end_time == None 
    ).first()
    if not db_usage_record:
        return None # 记录不存在或已结束

    # 2. 计算结束时间和使用时长
    # 统一使用带时区的当前时间
    end_time_beijing = datetime.now(beijing_tz)
    start_time_beijing = db_usage_record.actual_start_time.replace(tzinfo=beijing_tz)

    # 计算时长（秒）
    duration_seconds = (end_time_beijing - start_time_beijing).total_seconds()
    
    # 向上取整计算分钟数
    #    - 使用 math.ceil() 函数实现向上取整
    #    - 例如 0.1 分钟 -> 1 分钟； 1.1 分钟 -> 2 分钟
    duration_minutes = math.ceil(duration_seconds / 60)
    
    # 确保最少为 1 分钟
    if duration_minutes < 1:
        duration_minutes = 1

    # 3. 将所有信息【明确地】赋给数据库模型对象
    #    确保前端传来的 usage_data 中的每一个字段都被用上了。
    db_usage_record.actual_end_time = end_time_beijing
    db_usage_record.total_minutes_used = duration_minutes
    db_usage_record.actual_user_name = usage_data.actual_user_name
    db_usage_record.actual_user_phone = usage_data.actual_user_phone
    db_usage_record.actual_user_group = usage_data.actual_user_group
    db_usage_record.remarks = usage_data.remarks
    
    # 4. 更新关联的预约状态为“已完成”
    db_reservation = db_usage_record.reservation
    if db_reservation:
        db_reservation.status = 'completed'

    # 5. 提交数据库事务，将所有改动一次性写入数据库
    db.commit()
    db.refresh(db_usage_record)
    return db_usage_record


def get_usage_record_details(db: Session, usage_record_id: int, user_id: int) -> Optional[models.UsageRecord]:
    """获取单个已完成订单（使用记录）的详细信息"""
    return db.query(models.UsageRecord).join(models.Reservation).filter(
        models.UsageRecord.id == usage_record_id,
        models.Reservation.creator_user_id == user_id
    ).first()